﻿using System;
using System.Collections.Generic;
using Microsoft.Data.Sqlite;
using StudentManagementConsole.Data;
using StudentManagementConsole.Models;

namespace StudentManagementConsole.Data
{
    public static class DatabaseHelper
    {
        private const string ConnString = "Data Source=school.db;";
        static DatabaseHelper() { Init(); }

        public static void Init()
        {
            using var conn = new SqliteConnection(ConnString);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS School(Id INTEGER PRIMARY KEY, Name TEXT);
CREATE TABLE IF NOT EXISTS Class(Id INTEGER PRIMARY KEY, Name TEXT, SchoolId INTEGER);
CREATE TABLE IF NOT EXISTS Student(Id INTEGER PRIMARY KEY, Name TEXT, ClassId INTEGER);
CREATE TABLE IF NOT EXISTS LogEntry(Id INTEGER PRIMARY KEY, Operation TEXT, Timestamp TEXT);
";
            cmd.ExecuteNonQuery();
        }

        public static void Log(string op)
        {
            using var conn = new SqliteConnection(ConnString);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO LogEntry(Operation,Timestamp) VALUES(@op,@ts)";
            cmd.Parameters.AddWithValue("@op", op);
            cmd.Parameters.AddWithValue("@ts", DateTime.Now.ToString("o"));
            cmd.ExecuteNonQuery();
        }

        public static void AddSchool(string name)
        {
            using var conn = new SqliteConnection(ConnString);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO School(Name) VALUES(@name)";
            cmd.Parameters.AddWithValue("@name", name);
            cmd.ExecuteNonQuery();
            Log($"Add School: {name}");
        }

        public static List<School> GetSchools()
        {
            var list = new List<School>();
            using var conn = new SqliteConnection(ConnString);
            conn.Open();
            var cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM School";
            using var rdr = cmd.ExecuteReader();
            while (rdr.Read()) list.Add(new School { Id = rdr.GetInt32(0), Name = rdr.GetString(1) });
            return list;
        }

        // TODO: 增加 Class、Student 的 CRUD 方法...

        public static List<LogEntry> GetLogs()
        {
            var logs = new List<LogEntry>();
            using var conn = new SqliteConnection(ConnString);
            conn.Open();
            var cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM LogEntry";
            using var rdr = cmd.ExecuteReader();
            while (rdr.Read()) logs.Add(new LogEntry { Id = rdr.GetInt32(0), Operation = rdr.GetString(1), Timestamp = DateTime.Parse(rdr.GetString(2)) });
            return logs;
        }
    }
}

